﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using ElectronicJournal.DB.Item;

namespace ElectronicJournal.DB
{
    public class DbJournals
    {
        private SqlDb db;

        public DbJournals()
        {
            db = new SqlDb();
        }

        public ItemJournal FetchRowJournal(DataRow row)
        {
            return new ItemJournal()
            {
                Year = Convert.ToInt32(row["JournalYear"].ToString()),
                Number = Convert.ToInt32(row["JournalNumber"].ToString()),
                Id = row["JournalId"].ToString(),
                FileName = row["FileName"].ToString()
            };
        }

        public List<ItemJournal> GetJournalInYear(int year)
        {
            List<ItemJournal> list = new List<ItemJournal>();
            string query = String.Format("SELECT * FROM Journal WHERE JournalYear = {0} ORDER BY JournalNumber", year);
            DataTable table = db.Read(query);
            foreach (DataRow row in table.Rows)
            {
                list.Add(FetchRowJournal(row));
            }
            return list;
        }

        public List<int> GetYears()
        {
            List<int> list = new List<int>();
            string query = "SELECT DISTINCT JournalYear FROM Journal ORDER BY JournalYear DESC";
            DataTable table = db.Read(query);
            foreach (DataRow row in table.Rows)
            {
                list.Add(Convert.ToInt32(row["JournalYear"].ToString()));
            }
            return list;
        }

        public ItemJournal GetJournalById(string id)
        {
            string query = String.Format("SELECT * FROM Journal WHERE JournalId = '{0}'", id);
            DataTable table = db.Read(query);
            if (table.Rows.Count > 0)
            {
                return FetchRowJournal(table.Rows[0]);
            }
            return null;
        }

        public bool AddNewJournal()
        {
            string query = String.Format("INSERT INTO Journal VALUES ('{0}', '{1}', NEWID())", DateTime.Now.Year, GetLastNumberThisYear(DateTime.Now.Year) + 1);
            try
            {
                db.Query(query);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public int GetLastNumberThisYear(int year)
        {
            int number = 0;
            string query = "SELECT JournalNumber FROM Journal WHERE JournalYear = " + year;
            DataTable table = db.Read(query);
            foreach (DataRow row in table.Rows)
            {
                int i = Convert.ToInt32(row["JournalNumber"].ToString());
                if (i > number) number = i;
            }
            return number;
        }

        public void UploadFile(string id, string filename)
        {
            string query = String.Format("UPDATE Journal SET FileName = '{0}' WHERE JournalId = '{1}'", filename, id);
            db.Query(query);
        }
    }
}